﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.Linq;
using System.Data.SqlServerCe;
using System.IO;

// EEJ Added usings
using ErikEJ.SqlCe;
using System.Reflection;

namespace BuildSDFDatabase
{
	public class DatabaseAccessor : IDisposable
	{
		private string dbSource = "Data Source=|DataDirectory|";
		private SqlCeConnection dbConnection;

		public DatabaseAccessor(string addr)
		{
			dbSource += addr;

			if (!File.Exists(addr))
			{
				SqlCeEngine eng = new SqlCeEngine(dbSource);
				eng.CreateDatabase();
			}

			dbConnection = new SqlCeConnection(dbSource);
			dbConnection.Open();
		}

		public void Dispose()
		{
			dbConnection.Close();
		}

		public void InsertOneItem(string tableName, List<string> values)
		{
			string execute = "insert into " + tableName + " values(";
			foreach (var value in values)
				execute += "\'" + value + "\',";
			execute = execute.TrimEnd(',') + ");";

			DBExecuteNonQuery(execute);
		}

		public void BulkInsert(DataTable dt)
		{
			using (SqlCeBulkCopy bc = new SqlCeBulkCopy(dbSource))
			{
				bc.DestinationTableName = dt.TableName;
				bc.WriteToServer(dt);
				Console.WriteLine(">>LOG>> finish {0} insertion.", dt.TableName);
			}
		}

		public void BulkInsert(List<DataTable> tables)
		{
			foreach (var table in tables)
				BulkInsert(table);
		}

		public void DeleteItemByValue(string tableName, string attribute, string value)
		{
			string execute = string.Format("delete from {0} where {1}={2};", tableName, attribute, value);
			DBExecuteNonQuery(execute);
		}

		public void DBExecuteNonQuery(string execute)
		{
			using (SqlCeCommand com = new SqlCeCommand(execute, dbConnection))
			{
				try
				{
					com.ExecuteNonQuery();
				}
				catch (Exception e)
				{
					throw new Exception(e.Message);
				}
			}
		}

		public SqlCeResultSet DBExecuteQuery(string execute, ResultSetOptions option)
		{
			using (SqlCeCommand com = new SqlCeCommand(execute, dbConnection))
			{
				try
				{
					SqlCeResultSet result = com.ExecuteResultSet(option);
					return result;
				}
				catch (Exception e)
				{
					throw new Exception(e.Message);
				}
			}
		}
	}
}
